package server.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import server.ServerEngin;

public class DAOUtil {
    
    private static DBConnectionManager  connectionMan=DBConnectionManager.getInstance();//得到唯一实例
    
    private static DAOUtil instance;
    
    private Connection  con;
    
    private String name="gameserver";//从上下文得到你要访问的数据库的名字
    
    public static DAOUtil getInstance(){
        if (instance == null){
			synchronized (DAOUtil.class) {
				if (null == instance) {
					instance = new DAOUtil();
				}
			}
        }
        return instance;
    }
    
    public List query(String sql,List params) throws JDBCException{
        
        List result = null;  
        
        PreparedStatement pstmt = null;  
        
        ResultSet rs = null;
        
        Connection  conn=connectionMan.getConnection(name);
        
        try {  
            
            pstmt = conn.prepareStatement(sql);  
            
            this.apply(pstmt, params);  
            
            rs = pstmt.executeQuery();  
            
            result = this.convert(rs);  
        } catch (SQLException ex) {  
            
            throw new JDBCException("can not execute query", ex);  
        } finally {
            
            connectionMan.freeConnection(name,con);//释放，但并未断开连接 
            
            if (pstmt != null) {  
                
                try {  
                    
                    pstmt.close();  
                } catch (SQLException e) {  
                    
                    // nothing  
                }  
            }  
        }
        return result;
        
    }
    
    private void apply(PreparedStatement pstmt, List params) throws JDBCException {
        
        try {
            // if params exist
            if (params != null && params.size() > 0) {
                // parameters iterator
                Iterator it = params.iterator();
                
                // parameter index
                int index = 1;
                
                while(it.hasNext()) {
                    
                    Object obj = it.next();
                    // if null set ""
                    
                    if (obj == null) {
                        
                        pstmt.setObject(index, "");
                    } else {
                        // else set object
                        pstmt.setObject(index, obj);
                    }
                    //next index
                    index++;
                }
            }
        
        } catch (SQLException ex) {
            
            throw new JDBCException("can not apply parameter", ex);
        }
    }
    
    private List convert(ResultSet rs) throws JDBCException {

        // record list
        List retList = new ArrayList();

        try {
            ResultSetMetaData meta = rs.getMetaData();

            // column count
            int colCount = meta.getColumnCount();

            // each record
            while (rs.next()) {

                Map recordMap = new HashMap();

                // each column
                for (int i = 1; i <= colCount; i++) {
                    // column name
                    String name = meta.getColumnName(i);
                    // column value
                    Object value = rs.getObject(i);
                    // add column to record
                    recordMap.put(name, value);
                }
                // ad record to list
                retList.add(recordMap);
            }
        } catch (SQLException ex) {
            throw new JDBCException("can not convert result set to list of map", ex);
        }
        return retList;
    }
    
    public int execute(String sql, List params) throws JDBCException {
        
        int ret = 0;
        
        PreparedStatement pstmt = null;
        
        try {
            
            Connection  conn=connectionMan.getConnection(name);
            
            pstmt = conn.prepareStatement(sql);
            
            this.apply(pstmt, params);
            
            ret = pstmt.executeUpdate();
        
        }catch(SQLException ex) {
            
            throw new JDBCException("excute "+sql+" faild!", ex);
        
        } finally {
            
            connectionMan.freeConnection(name,con);//释放，但并未断开连接
            
            if (pstmt != null) {
                
                try {
                    
                    pstmt.close();
                
                } catch (SQLException e) {
                    // nothing.
                }
            }
        }
        
        return ret;
    }

}
